<?php
/**
* This file belongs to a webappkit package (kit)
* @link http://webappkit.net
* @package databeans
* @subpackage sqlrecord
* @author J.Ducastel <nospam0@ducastel.name>
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*/

/**
* A somewhat free ActiveRecord implementation.
* you have to define or generate methods for sql queries
* @uses dateTimeBean
*/
class sqlRecordBean extends dataBean {
/*------------------------------------------------------------------------------
                                                                     constructor
------------------------------------------------------------------------------*/
    /**
    * constructor alias
    * @access public
    * /
    function sqlRecordBean() {
        $this->__construct();
    }/**/

    /**
    * "real" constructor
    * @access public
    * /
    function __construct() {

    }/**/
/*------------------------------------------------------------------------------
                                                                  public methods
------------------------------------------------------------------------------*/
    /**
    * @deprecated
    * @static
    * @param
    * @access public
    * @return wakSqlRecordBean|false
    * /
    function & getByUniqueId($uniqueId) {
        //$class=__CLASS__; echo $class;
        //if (!$this) $this=new $class();
        $result=$this->_getQueryResult('getbyuniqueid',array('id'=>$uniqueId));
    }/**/

    /**
    * loads bean from database (after than id/primary key has been set)
    * @access public
    * @return bool
    */
    function load() {
        if (!$this->_loadFromQuery('load'))
            return false;
        $this->_afterLoad();
        return true;
        // checking global validity
        return $this->globalCheck();
    }/**/

    /**
    * insert bean into database.
    * = executes query insert
    * In case of insertion, id will be retrieved with mysql_last_insert_id
    * @access public
    * @return bool
    */
    function insert() {
        if (!$result=$this->_runQuery('insert')) {
            return false;
        }
        $this->_afterInsert();
        return true;
    }/**/

    /**
    * execute 'update' query if available
    * @access public
    * @return bool
    */
    function update() {
        if (!$this->_runQuery('update'))
            return false;
        $this->_afterUpdate();
        return true;
    }/**/

    /**
    * execute 'replace' query if available
    * @access public
    * @return bool
    */
    function replace() {
        if (!$this->_runQuery('replace'))
            return false;
        $this->_afterUpdate();
        return true;
    }/**/

    /**
    * Saves bean into database.
    * For simple structures using 1 table, use a REPLACE sql statement named 'save'
    * In case of insertion, id will be retrived with mysql_last_insert_id
    * @access public
    * @return bool
    */
    function save() {
        if (!$result=$this->_runQuery('save')) {
            return false;
        }
        $this->_afterSave();
        return true;
    }/**/

    /**
    * Searches for bean sharing current bean properties
    * runs query 'search'
    * @param array $data data for queries, overriding own data
    * @param string $orderby order by statement
    * @param string limit statement
    * @access public
    * @return array databeans array
    */
    function search($data=array(),$orderby=null,$limit=null) {
        return $this->_beansFromQuery('search',$data,$orderby,$limit);
    }/**/

    /**
    * counts some data.
    * issue query named count, gets value for field named 'count'
    * @param array $data
    * @access public
    * @return int
    */
    function count($data=array()) {
        // getting sql result
        if (!$r=$this->_runQuery('count',$data))
            return false;
        // fetching line
        if (!$line=mysql_fetch_assoc($r))
            return false;
        // returning value
        return intval($line['count']);
    }/**/

    /**
    * lists rows
    * @param string $orderby
    * @param string $limit
    * @access public
    * @return array sqlrecordbeans
    */
    function listall($orderby=null,$limit=null) {
        return $this->_beansFromQuery('listall',$orderby,$limit);
    }/**/

    /**
    * executes 'delete' query if available
    * @access public
    * @return bool
    */
    function delete() {
        return $this->_runQuery('delete');
    }/**/

    /**
    * returns last issued sql query
    * @access public
    * @return string
    */
    function lastquery() {
        return $this->_lastquery;
    }/**/

    /**
    * returns last sql error
    * @access public
    * @return string
    */
    function lastSqlError() {
        return $this->_lastsqlerror;
    }

    /**
     * get the bean as a csv string
     * @access public
     * @param string $delimiter default is ';'
     * @return string
     */
    function getAsCsv($delimiter=';') {
        return join($delimiter,$this->getAllAssoc());
    }

    /**
     * get the members as a csv header line
     * @param string $delimiter default is ';'
     * @return string
     */
    function getCsvHeader($delimiter=';') {
        return join($delimiter,$this->listMembers());
    }

    /**
    * @var bool enable/disable setting undeclared members
    * changed by membersLock()
    */
    var $_databean_members_lock=true;

    /**
    * @var string primary key member
    */
    var $_primarykey='id';

    /**
     * @var string  last executed query
     */
    var $_lastquery;

    /**
     * @var string last SQL error
     */
    var $_lastsqlerror;

    /**
     * alias for runquery
     */
    function & _sqlRecordBean_runQuery($queryId,$data=array(),$orderby=null,$limit=null) {
        return $this->_runQuery($queryId,$data,$orderby,$limit);
    }
    /**
    * will build an identified query and return its result.
    * Injects custom data, or actual values.
    * Will build the query from the first found of :
    * - the method _query_{id}
    * - the method _queryTpl_id as a query template.
    * - the property _query_{id} as query template.
    * @param string $queryId query internal id string
    * @param array $data custom data overriding current values
    * @param string $orderby if present, adds an ORDER BY statement
    * @param string $limit if present, adds a LIMIT statement
    * @access protected
    * @see _feedTemplate
    * @return resource|false sql result resource
    */
    function & _runQuery($queryId,$data=array(),$orderby=null,$limit=null) {
        $r=false;
        // searching for query building procedure
        // from custom method
        if (method_exists($this,'_query_'.$queryId)) {
            $method='_query_'.$queryId;
            $query=$this->$method($data);
        // from query-template returning method
        } else if (method_exists($this,'_querytpl_'.$queryId)) {
            //echo "<p>got query $queryId from template method</p>";
            $method='_querytpl_'.$queryId;
            $query=$this->$method();
            //echo $query;
        // from query-template property
        } else {
            $queryProp='_query_'.$queryId;
            if (!isset($this->$queryProp)) {
                //throw new Exception(get_class($this)." : query $queryId don't exists",500);
                return $r;
            } else
                $query=$this->$queryProp;
        }
        // feeding query template
        $query=$this->_feedTemplate($query,$data); //echo $query;
        // adding orderby, limit statements
        if ($orderby)
            $query.=' ORDER BY '.$orderby;
        if ($limit)
            $query.=' LIMIT '.$limit;
        // executing query
        $r=@mysql_query($query);
        if (!$r) {
            $this->_lastsqlerror=mysql_error();
            //throw new Exception('SQL error : '.$this->_lastsqlerror,500);
        }
        $this->_lastquery=$query;
        //$query=$this->_feedtemplate($query,$data);
        return $r;
    }/**/

    /**
     * alias for feedtemplate
     */
    function & _sqlRecordBean_feedTemplate($query,$data=array()) {
        return $this->_feedtemplate($query,$data);
    }
    /**
    * feeds query template with current bean properties values.
    * replaces vars (strings starting by $) with their value
    * search for blocks between double accolads {{ opt text $key opt text}}
    * opt text can't include accolad
    *
    * @param string $query
    * @param array $data optional, values to use instead of current members values
    * @access protected
    * @return string
    */
    function & _feedTemplate($query,$data=array()) {

        //$deb=wak::getservice('wakdebug');
        // optional replacement
        $n=0; $ereg='\{\{([^{}]*)\$([a-zA-Z][a-zA-Z0-9_]*)(!)?([^{}]*)?\}\}';
        while (ereg($ereg,$query,$found)) {
            // avoiding infinite loop
            $n++; if ($n>50) break;
            $before=$found[1];
            $key=$found[2];
            $force=$found[3];
            $after=$found[4];
            // building replacement block
            if (isset($data[$key]) and !is_null($data[$key])) {
                // provided not null value
                if ($force)
                    $dbval=strval($data[$key]);
                else
                    $dbval=$this->_valueToDb($data[$key]);
                $replace=$before.$dbval.$after;
            } else if (isset($this->$key) and !is_null($this->$key)) {
                // taking not null member value
                $dbval=$this->_memberToDb($key); //echo $dbval;
                $replace=$before.$dbval.$after;
            } else {
                // null value
                $replace='';
            }
            // replacing
            // escaping regex special chars
            $before=str_replace('(','\(',$before);
            $before=str_replace(')','\)',$before);
            $after=str_replace('(','\(',$after);
            $after=str_replace(')','\)',$after);
            $ereg2='\{\{'.$before.'\$'.$key.($force?$force:'').$after.'\}\}';
            $query=ereg_replace($ereg2,$replace,$query);
        }
        // non-optional value replacement
        $n=0; $ereg='\$([a-zA-Z][a-zA-Z0-9_]*)(!)?';
        while (ereg($ereg,$query,$found)) {
            // avoiding infinite loop
            $n++; if ($n>50) break;
            // echo wakaffvar($found);
            $key=$found[1];
            $force=$found[2];
            // building replacement
            if (isset($data[$key])) {
                // using provided not null value
                $dbval=$this->_valueToDb($data[$key]);
            } else {
                // taking not null member value
                $dbval=$this->_memberToDb($key); //echo $dbval;
            }
            //echo "dbval $dbval";
            // replacing
            $ereg2='\$'.$key.($force?$force:'');
            // $ereg2=$found[0];
            // echo $ereg2;
            $query=ereg_replace($ereg2,$dbval,$query);
        }
        //echo $query;
        return $query;
    }/**/

    /**
    * converts a value for use within sql query (quote etc).
    * @param string $key bean's key name
    * @param mixed $value optional, if omitted current value will be used
    * @access protected
    * @return string
    */
    function _memberToDb($key,$value=null) {
        // getting current value if not provided
        if (is_null($value))
            $value=$this->getMember($key);
        // searching for custom method
        if ($this->_databean_callCustomMethod("_{$key}_toDb",$value))
            return $value;
        // searching for sql-type related conversion methods
        /*$keySqlType="_$key_sqltype";
        if (isset($this->$keySqlType))
            return $this->_sqlRecordBean_toDbUponSqlType($value,$this->$keySqlType);*/
        // standard conversion
        $value=$this->_sqlRecordBean_valueToDb($value);
        return $value;
    }
    /**
    * alias for _memberToDb
    */
    function _sqlRecordBean_memberToDb($key,$value=null) {
        return $this->_memberToDb($key,$value);
    }

    /**
    * converts and import a value from db extraction value.
    * @param string $key
    * @param mixed $value
    * @access protected
    * @return bool
    */
    function _setMemberFromDb($key,&$value) {
        // checking members lock if member unknwown
        if (!$this->hasMember($key) and $this->membersLock()) {
            $this->_throwError("cannot set member from db ; member is unkown and bean is locked");
            return false;
        }
        // converting value from db by custom method
        if (method_exists($this,'_'.$key.'_fromDb')) {
            $method='_'.$key.'_fromDb';
            $value=$this->$method($value);
        // converting value using standard input filter
        } else if (method_exists($this,'_'.$key.'_inputFilter')) {
            $method='_'.$key.'_inputFilter';
            $value=$this->$method($value);
        }
        // checking validity
        /*if (!$this->checkMember($key,$value))
            return false;*/
        // finally setting value
        $this->$key=$value;
        return true;
    }
    /**
     * alias for _setMemberFromDb
     */
    function _sqlRecordBean_setMemberFromDb($key,&$value) {
        return $this->_setMemberFromDb($key,$value);
    }

    /**
    * loads own data from sql query.
    * @param string $query
    * @access protected
    * @return bool
    */
    function _loadFromQuery($query) {
        if (!$dbResult=$this->_runQuery($query))
            return false;
        if (!$data=@mysql_fetch_assoc($dbResult))
            return false;
        // loading members
        // echo wakaffvar($data);
        foreach (array_keys($data) as $key)
            $this->_sqlRecordBean_setMemberFromDb($key,$data[$key]);
        return true;
    }
    /***
     * alias for _loadfromquery
     */
    function _sqlrecordBean_loadFromQuery($query) {
        return $this->_loadfromquery($query);
    }

    /**
    * executes a sql query, and returns beans from result
    * returns current class beans
    * @param string $query sql query
    * @param array $data
    * @param string $orderby
    * @param string $limit
    * @access protected
    * @return sqlRecordBean
    */
    function & _beansFromQuery($query,$data=array(),$orderby=null,$limit=null) {
        $list=array();
        // getting db result
        $dbResult=$this->_runQuery($query,$data,$orderby,$limit);
        while ($line=@mysql_fetch_assoc($dbResult)) {
            $class=get_class($this); // echo $class;
            $bean=new $class();
            // converting fields
            foreach (array_keys($line) as $key)
                $bean->_setMemberFromDb($key,$line[$key]);
            //$bean=new $class($data);
            // echo $deb->vardump($bean);
            $list[]=$bean;
        }
        return $list;
    }
    /**
     * alias for _beansFromQuery
     */
    function & _sqlRecordBean_beansFromQuery($query,$data=array(),$orderby=null,$limit=null) {
        return $this->_beansFromQuery($query,$data,$orderby,$limit);
    }

    /**
    * converts value from database upon provided sql type
    * @param string $type
    * @param mixed $value
    * @access protected
    * @return string
    * @deprecated
    */
    function _sqlRecordBean_fromDbUponSqlType($type,$value) {
        switch ($type) {
            case 'int':
            case 'integer':
            case 'smallint':
            case 'tinyint':
            case 'bigint':
                return intval($value);
            // types date et valeur datetimebean
            // case 'date'
            /*case 'date':
            case 'datetime':
            case 'timestamp':
            //case
                if (class_exists('datetimebean'))
                    return new datetimebean($value);*/
            default:
                return $value;
        }
        //return intval($value);
    }/**/

    /**
    * prepares data array for sql use.
    * uses current member values unless keys are provided in $data.
    * @access protected
    * @param array $data
    * @return array
    */
    function & _dataToDb($data=array()) {
        // merging provided keys with class keys
        $keys=array_merge($this->_databean_getMembers(),$data);
        // cycling
        foreach ($keys as $key) {
            if (isset($data[$key]))
                $data[$key]=$this->_valueToDb($data[$key]);
            else
                $data[$key]=$this->_memberToDb($key);
        }
        return $data;
    }
    /**
     * alias for _dataToDb
     */
    function & _sqlRecordBean_dataToDb($data=array()) {
        return $this->_dataToDb($data);
    }

    /**
    * converts a value for use into sql queries.
    * @param mixed $value
    * @access protected
    * @return string
    */
    function _valueToDb($value) {
        switch (true) {
            case is_null($value):
                $value='NULL';
                break;
            case is_string($value):
                $value=mysql_real_escape_string($value);
                $value='\''.$value.'\'';
                break;
            case is_int($value):
                $value=strval($value);
                break;
            case is_bool($value):
                $value=$value?'1':'0';
                break;
            case is_a($value,'datetimebean'):
                $value='\''.$value->toMysqlDateTime().'\'';
        }
        return $value;
    }
    /**
     * alias for _valueToDb
     */
    function _sqlRecordBean_valueToDb($value) {
        return $this->_valueToDb($value);
    }

    /**
    * returns structure primary key name
    * @access protected
    * @return string
    */
    function _primaryKey() {
        return isset($this->_primarykey)?$this->_primarykey:'id';
    }/**/

    /**
    * executes some actions after loading bean
    * @access protected
    * @return bool
    */
    function _afterLoad() {
        //$this->_sqlRecordBean_getInsertId();
        return true;
    }/**/

    /**
    * executes some actions after saving bean
    * @access protected
    * @return bool
    */
    function _afterSave() {
        $this->_getInsertId();
        return true;
    }/**/

    /**
    * executes some actions after inserting bean
    * @access protected
    * @return bool
    */
    function _afterInsert() {
        $this->_getInsertId();
        return true;
    }/**/

    /**
    * executes some actions after updating bean
    * @access protected
    * @return bool
    */
    function _afterUpdate() {
        return true;
    }/**/

    /**
    * retrieves auto_incremented insert id
    * @access protected
    * @return bool
    */
    function _getInsertID() {
        // setting primary key from last insert id if necessary
        if ($primKey=$this->_primaryKey()) {
            if (is_null($this->$primKey))
                $this->_setMemberFromDb($primKey,mysql_insert_id());
                //$this->$primKey=mysql_insert_id();
            return $this->memberCheck($primKey,$this->$primKey);
        }
        return false;
    }/**/

/*------------------------------------------------------------------------------
                                                                 private methods
------------------------------------------------------------------------------*/
    /**
    * converts value to datetimebean instance or null
    * @param mixed $value
    * @access private
    * @return DateTimebean|null
    */
    function & _toDateTimeBean($value) {
        if (class_exists('datetimebean') and !is_null($value) and $value)
            return new datetimebean($value);
        else
            return null;
    }/**/

    /**
    * converts to integer if string made of digits
    * @param mixed $value
    * @access private
    * @return int
    */
    function & _toInteger($value) {
        if (is_int($value))
            return $value;
        else if (is_string($value) and $value
        and strval(intval($value))==$value)
            $value=intval($value);
        else $value=null;
        return $value;
    }/**/

    /**
    * check that is a valid mysql date format
    * @param string $x
    * @access private
    * @return bool
    */
    function _isMysqlDate($x) {
        return is_string($x) and ereg('^[0-9]{4}-[0-9]{2}-[0-9]{2}$',$x);
    }/**/
}